/* BESEDES-PRUSA USING EXPORT DATA FROM SPANISH REGIONS 1988-2006 */


drop _all
u world_exports_price_deflator_1980_2007
sort year
ge deflator=(price/152.1)*100  /* 2005=100 */
keep if year>=1988 & year<=2006
keep year deflator
sort year
save world_exports_price_deflator_1988_2006, replace


drop _all
u codepais
sort c_wdi
save codepais, replace


drop _all
set mem 500000k
u exp_ccaa_pais_sitc_1988_2006.dta
drop if sitc<=50000
drop if sitc>=89999
sort c_wdi
merge c_wdi using codepais
drop if _merge!=3
drop _merge

sort year
merge year using world_exports_price_deflator_1988_2006
drop if _merge!=3
drop _merge

replace valor=(valor/deflator)*100

keep year nreg ccaa iso3 c_wdi sitc valor 

rename nreg reporter_iso3 
rename ccaa region 
rename iso3 partner_iso3 
rename sitc commodity_code 
rename valor value 

save besedes_prusa_espanya_sample, replace




/* ******** */
cd file_aux					/* work in auxiliar file */
save besedes_prusa_espanya_sample, replace
/* ********* */

local year0 "1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  
local year1 "1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  

set more off
foreach yr of local year0 {
drop _all
u besedes_prusa_espanya_sample
keep if year==`yr'
keep year reporter_iso3 region partner_iso3 commodity_code value 
save brusa_yr`yr', replace
}
set more on


local cou_iso3 "AND ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  
local cou_iso3_1 "ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  

local year0 "1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  
local year1 "1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  



/* SURVIVAL DATASET DESTINATION BY DESTINATION  */

set more off
foreach zz of local cou_iso3 {
	foreach yr0 of local year0 {
		drop _all
		u brusa_yr`yr0'
		keep if reporter_iso3=="`zz'"
		keep reporter_iso3 partner_iso3 commodity_code year value
		sort reporter_iso3 year
		save temp_`zz'_`yr0', replace
	}
	drop _all
	u temp_`zz'_1988, replace
		foreach yr1 of local year1 {
		append using temp_`zz'_`yr1'
		}
order reporter_iso3 partner_iso3 commodity_code year
sort reporter_iso3 partner_iso3 commodity_code year

ge spell=.
replace spell=_n if reporter_iso3==reporter_iso3[_n-1] & partner_iso3==partner_iso3[_n-1] & commodity_code!=commodity_code[_n-1] 
replace spell=_n if spell==. & reporter_iso3==reporter_iso3[_n-1] & partner_iso3==partner_iso3[_n-1] & commodity_code==commodity_code[_n-1] & year!=year[_n-1]+1 & year[_n-1]!=.
replace spell=_n if spell==. & reporter_iso3==reporter_iso3[_n-1] & partner_iso3!=partner_iso3[_n-1] 
replace spell=_n if spell==. & _n==1
sort reporter_iso3 partner_iso3 commodity_code year spell
quietly by reporter_iso3 partner_iso3 commodity_code: replace spell=spell[_n-1] if spell==. & spell[_n-1]!=.

/* variable year_begin */
sort spell year
quietly by spell: egen begin=min(year)
replace begin=begin-1 /* necessary for survival analysis */
label var begin "year before spell (reporter-partner-industry) starts"

/* variable expseq */
sort spell year
ge expseq=.
quietly by spell: replace expseq=year-begin	
label var expseq "years exporting 1,2,3,..."

/* variable year_end */
quietly by spell: egen end=max(year)
label var end "year spell (reporter-partner-industry) ends"

/* variable failure */
sort spell year
ge failure=.
quietly by spell: replace failure=1 if year==end & year!=2006
replace failure=0 if failure==.
label var failure "=1 the year spell stops"

/* variable left censored */
ge leftcensored=.
replace leftcensored=1 if year==1988 & value!=.
quietly by spell: replace leftcensored=leftcensored[_n-1] if year>1988
replace leftcensored=0 if leftcensored==.
label var leftcensored "=1 if left censored i.e. firms exported in 1988"

order reporter_iso3 partner_iso3 commodity_code year value spell begin end expseq failure leftcensored
save cou_`zz', replace
}





/* *************************************** */
/* 			TABLE 1			 */
/* *************************************** */

drop _all

local cou_iso3 "AND ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  
local cou_iso3_1 "ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  

local year0 "1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  
local year1 "1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  

set more off
foreach zz of local cou_iso3 {
	u cou_`zz', replace
	ge n_obs=_N
	egen t_spell=count(spell) if spell!=spell[_n-1]
	egen n_spell=max(t_spell)
	drop t_spell
	keep reporter_iso3 n_obs n_spell
	keep if _n==1
	save aux_table1_save_`zz', replace
}

drop _all
u aux_table1_save_AND
foreach zz of local cou_iso3_1 {
	append using aux_table1_save_`zz'
}

cd ..
log using res_table1.text, text replace
/* *************************************** */
/* 			TABLE 1		   */

l , clean

/* *************************************** */
log close
cd file_aux










/* ****************************************************************************** */
/* TABLE 2: RELATIONSHIP BETWEEN EXPORT GROWTH AND GROWTH IN EXPORT RELATIONSHIPS */
/* ****************************************************************************** */


drop _all
set mem 500000k

local cou_iso3 "AND ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  
local cou_iso3_1 "ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  

local year0 "1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  
local year1 "1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  


set more off
foreach yr0 of local year0 {
	drop _all
	u brusa_yr`yr0'
	sort commodity_code
	egen t_industry=count(year) if commodity_code!=commodity[_n-1]
	egen cc_industry=max(t_industry)
	sort partner_iso3
	egen t_partner=count(year) if partner!=partner_iso3[_n-1]
	egen cc_partner=max(t_partner)
	keep year cc_industry cc_partner
	keep if _n==1
	save aux_year_table2_`yr0', replace
}

set more off
drop _all
u aux_year_table2_1988
foreach yr1 of local year1 {
	append using aux_year_table2_`yr1'
}	
sort year
save number_ind_and_partner_by_year, replace



set more off
foreach zz of local cou_iso3 {
u cou_`zz', replace
sort year
merge year using number_ind_and_partner_by_year
drop if _merge!=3
drop _merge
ge maxrel=cc_industry*cc_partner
label var maxrel "maximum possible number relationships"

sort year spell 
egen nt=count(spell) ,by(year)
label var nt "number spells in t"

egen vt_nt=sum(value), by(year)
label var vt_nt "weighted number spells in t" 

egen t_n0t=count(spell) if expseq==1 ,by(year)
egen n0t=max(t_n0t), by(year)
drop t_n0t
label var n0t "number spells in first year in t" 

egen ts_n0t=sum(value) if expseq==1, by(year)
egen v0t_n0t=max(ts_n0t), by(year)
drop ts_n0t
label var v0t_n0t "weighted number spells in first year in t" 

* column 3 and 4
ge perc_rel=nt/maxrel
label var perc_rel "percentage rel in potential rel"
* column 5
ge m_ext=n0t/nt
* column 6
ge m_w_ext=v0t_n0t/vt_nt

keep reporter_iso3 year nt vt_nt perc_rel m_ext m_w_ext
ge perc_rel_1988=perc_rel if year==1988
ge perc_rel_2006=perc_rel if year==2006
ge nt_1988=nt if year==1988
ge nt_2006=nt if year==2006
ge vt_nt_1988=vt_nt/1000000 if year==1988
ge vt_nt_2006=vt_nt/1000000 if year==2006

replace m_ext=. if year==1988
replace m_w_ext=. if year==1988

collapse vt_nt_1988 vt_nt_2006 nt_1988 nt_2006 perc_rel_1988 perc_rel_2006 m_ext m_w_ext 
ge reporter_iso3="`zz'"
save table2_`zz', replace
}

set more off
u table2_AND, replace
foreach zz of local cou_iso3_1 {
append using table2_`zz'
}

sort reporter_iso3
order reporter_iso3


cd ..
log using res_table2.text, text replace
/* ******************************************** */
/* 		TABLE 2 					*/

l , clean
/* ********************************************* */
log close
cd file_aux












/* ******************************************** */
/* 		TABLE 3 					*/
/* 								*/
/* ********************************************* */


drop _all
set mem 500000k

local cou_iso3 "AND ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  
local cou_iso3_1 "ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  

local year0 "1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  
local year1 "1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  


* unconditional failure rate
* ***************************
set more off
foreach zz of local cou_iso3 {
u cou_`zz', replace
egen cc_spell=count(spell), by(year)
egen t_failure=count(failure) if failure==1, by(year)
egen cc_failure=max(t_failure), by(year)
ge avg_failure=cc_failure/cc_spell
keep if year>=1989 & year<=2006
collapse avg_failure
ge reporter_iso3="`zz'"
save aux_table3_col1_`zz', replace
}


* survival analysis 
* **************************
set more off
foreach zz of local cou_iso3 {
u cou_`zz', replace

drop if leftcensored==1
ge year0=year-1
stset year, f(failure) id(spell) origin(begin) time0(year0)

sts gen surv=s		/*survivor function*/
label var surv "% surviving rel at time t"
sts gen haz=h		  /*hazard function: a partir de hazard contribution*/
label var haz "exit risk conditionated to be until t"

* sts graph
* table expseq, c(m surv m haz)

sort expseq
keep if expseq!=expseq[_n-1]
keep reporter_iso3 expseq surv haz
reshape wide surv haz, i(reporter_iso3) j(expseq)
ge haz1a2=(haz1+haz2)/2
ge haz3a5=(haz3+haz4+haz5)/3
ge haz6a12=(haz6+haz7+haz8+haz9+haz10+haz11+haz12)/7
ge cumfail1a12=1-surv12
keep reporter_iso3 haz1a2 haz3a5 haz6a12 cumfail1a12
save aux_table3_col2a4_`zz', replace

}



drop _all
u aux_table3_col1_AND, replace
foreach xx of local cou_iso3_1 {
append using aux_table3_col1_`xx'
}
sort reporter_iso3
save table3_col1_final, replace


u aux_table3_col2a4_AND, replace
foreach cc of local cou_iso3_1 {
append using aux_table3_col2a4_`cc'
}
sort reporter_iso3
save table3_col2a4_final, replace


drop _all
u table3_col1_final
merge reporter_iso3 using table3_col2a4_final	
drop if _merge!=3
drop _merge
sort reporter_iso3
order reporter_iso3


cd ..
log using res_table3.text, text replace
/* ******************************************** */
/* 		TABLE 3 					*/
/* SURVIVAL ANALYSIS    */
l , clean
/* ********************************************* */
log close
cd file_aux






/* ****************************************************************************** */
/* TABLE 4: DEEPENING (LONG TERM RELATIONSHIPS)						  */
/* ****************************************************************************** */


drop _all
set mem 500000k

local cou_iso3 "AND ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  
local cou_iso3_1 "ARA AST BAL CAN CAT CMA CLE CTN EXT GAL MAD MUR NAV PVS RIO VAL"  

local year0 "1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  
local year1 "1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006"  


set more off
foreach zz of local cou_iso3 {
u cou_`zz', replace
sort spell year
egen max_duration=max(expseq), by (spell)
keep if year==1988 | year==2006
sort max_duration spell year 

* table 4 column 1 
egen nrel_2006=count(spell) , by(year)
egen nrel_2006_1988=count(spell) , by(year max_duration)
ge f_nrel_2006=nrel_2006_1988/nrel_2006
label var f_nrel_2006 "fraction of 2006 rel"

* table 4 column 2 
egen vrel_2006=total(value), by(year)
egen vrel_2006_1988=total(value) ,by(year max_duration)
ge f_vrel_2006=vrel_2006_1988/vrel_2006
label var f_vrel_2006 "fraction of 2006 trade value"

* table 4 column 3
ge avg_g_x=(((vrel_2006_1988/vrel_2006_1988[_n-1])^(1/19))-1)*100 

keep if year==2006 & max_duration==19


collapse f_nrel_2006 f_vrel_2006 avg_g_x
ge reporter_iso3="`zz'"
save aux_table4_col1a3_`zz', replace
}



set more off
u aux_table4_col1a3_AND, replace
foreach zz of local cou_iso3_1 {
append using aux_table4_col1a3_`zz'
}
order reporter_iso3
save table4_final, replace


cd ..
log using res_table4.text, text replace
/* ******************************************** */
/* 		TABLE 4 							 */
/* 	LONG TERM RELATIONSHIPS  */

l , clean
/* ********************************************* */
log close




